﻿Imports BE.BE

Namespace DAL


    Public Class DAL_Usuario



        Shared Sub Alta(ByVal usr As Usuario_BE)
            Dim pmaxid As Integer = ObtenerProximoId()
            usr.id = pmaxid
            Dim mstrcomm = "INSERT INTO usuario ([id_usuario],[id_idioma],[nombre],[apellido],[nick],[password],[bloqueado],[intentos_inc],[password_reset],[borrado])" & _
                                      " VALUES (" & pmaxid & "," & usr.ididioma & ",'" & usr.nombre & "','" & usr.apellido & "','" & usr.nick & _
                                      "','" & usr.password & "','" & usr.bloqueado & "'," & usr.intentosfallidos & ",'" & usr.passwordreset & "','false')"
            Try
                Helper_DAL.ExecuteNonQuery(mstrcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Sub Borrar(ByVal usr As Usuario_BE)
            'se borra lógicamente el usuario seleccionado.
            Dim mstrcomm As String
            mstrcomm = "UPDATE usuario SET borrado=1 WHERE id_usuario = " & usr.id
            Try
                Helper_DAL.ExecuteNonQuery(mstrcomm)
            Catch ex As Exception
                Throw ex
            End Try

        End Sub


        Shared Sub Modificar(ByVal usr As Usuario_BE)
            Dim mstrcomm = "UPDATE usuario  SET [id_idioma] = " & usr.ididioma & ",[nombre] = '" & usr.nombre & "', [apellido] = '" & usr.apellido & _
                                      "',[nick] = '" & usr.nick & "',[bloqueado] = '" & usr.bloqueado & "',[intentos_inc] = " & usr.intentosfallidos & ",[password_reset] = '" & usr.passwordreset & _
                                      "' WHERE id_usuario = " & usr.id
            Try
                Helper_DAL.ExecuteNonQuery(mstrcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Function listar() As List(Of Usuario_BE)
            Dim Ds As New DataSet
            Ds = Helper_DAL.ExecuteDataset("SELECT * FROM usuario WHERE borrado = 0")
            Dim lista As New List(Of Usuario_BE)

            For Each dr As DataRow In Ds.Tables(0).Rows
                Dim usr As New Usuario_BE
                usr.id = dr.Item(0)
                usr.ididioma = dr.Item(1)
                usr.nombre = dr.Item(2)
                usr.apellido = dr.Item(3)
                usr.nick = dr.Item(4)
                usr.password = dr.Item(5)
                usr.bloqueado = dr.Item(6)
                usr.intentosfallidos = dr.Item(7)
                usr.passwordreset = dr.Item(8)
                usr.Patentes = DAL_Patente.listarPatentesOtorgadas(usr.id)
                usr.Familias = DAL_Familia.listarFamiliasPorUsuario(usr.id)
                lista.Add(usr)
            Next
            Return lista
        End Function




        Shared Function traerUsuario(ByVal pnick As String) As Usuario_BE
            Dim Ds As New DataSet
            Dim str As String
            str = "SELECT [id_usuario],[id_idioma],[nombre],[apellido],[nick],[password],[bloqueado],[intentos_inc],[password_reset] FROM usuario WHERE borrado = 0 AND nick = '" & pnick & "'"
            Ds = Helper_DAL.ExecuteDataset(str)


            If Ds.Tables(0).Rows.Count = 0 Then
                Return Nothing
            End If

            Dim usr As New Usuario_BE

            For Each dr As DataRow In Ds.Tables(0).Rows
                usr.id = dr.Item(0)
                usr.ididioma = dr.Item(1)
                usr.nombre = dr.Item(2)
                usr.apellido = dr.Item(3)
                usr.nick = dr.Item(4)
                usr.password = dr.Item(5)
                usr.bloqueado = dr.Item(6)
                usr.intentosfallidos = dr.Item(7)
                usr.passwordreset = dr.Item(8)
                'traer las patentes otorgadas.
                usr.Patentes = DAL_Patente.listarPatentesOtorgadas(usr.id)
                'traer las familias asociadas.
                usr.Familias = DAL_Familia.listarFamiliasPorUsuario(usr.id)
                Exit For
            Next
            Return usr
        End Function


        Shared Function traerUsuarioPorId(ByVal pid As Integer) As Usuario_BE
            Dim Ds As New DataSet
            Dim str As String
            str = "SELECT [id_usuario],[id_idioma],[nombre],[apellido],[nick],[password],[bloqueado],[intentos_inc],[password_reset] FROM usuario WHERE borrado = 0 AND id_usuario = " & pid
            Ds = Helper_DAL.ExecuteDataset(str)

            If Ds.Tables(0).Rows.Count = 0 Then
                Return Nothing
            End If

            Dim usr As New Usuario_BE

            For Each dr As DataRow In Ds.Tables(0).Rows
                usr.id = dr.Item(0)
                usr.ididioma = dr.Item(1)
                usr.nombre = dr.Item(2)
                usr.apellido = dr.Item(3)
                usr.nick = dr.Item(4)
                usr.password = dr.Item(5)
                usr.bloqueado = dr.Item(6)
                usr.intentosfallidos = dr.Item(7)
                usr.passwordreset = dr.Item(8)
                Exit For
            Next
            Return usr
        End Function


        Shared Function traerUsuarioPorIdActivosYBorrados(ByVal pid As Integer) As Usuario_BE
            Dim Ds As New DataSet
            Dim str As String
            str = "SELECT [id_usuario],[id_idioma],[nombre],[apellido],[nick],[password],[bloqueado],[intentos_inc],[password_reset] FROM usuario WHERE id_usuario = " & pid
            Ds = Helper_DAL.ExecuteDataset(str)

            If Ds.Tables(0).Rows.Count = 0 Then
                Return Nothing
            End If

            Dim usr As New Usuario_BE

            For Each dr As DataRow In Ds.Tables(0).Rows
                usr.id = dr.Item(0)
                usr.ididioma = dr.Item(1)
                usr.nombre = dr.Item(2)
                usr.apellido = dr.Item(3)
                usr.nick = dr.Item(4)
                usr.password = dr.Item(5)
                usr.bloqueado = dr.Item(6)
                usr.intentosfallidos = dr.Item(7)
                usr.passwordreset = dr.Item(8)
                Exit For
            Next
            Return usr
        End Function



        Public Shared Sub bloquearUsuario(ByVal pnick As String)
            Dim mstrcomm = "UPDATE usuario  SET [bloqueado] = 1 WHERE nick = '" & pnick & "'"
            Try
                Helper_DAL.ExecuteNonQuery(mstrcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Public Shared Sub desbloquearUsuario(ByVal pid As Integer)
            Dim mstrcomm = "UPDATE usuario  SET [bloqueado] = 0 WHERE id_usuario = " & pid
            Try
                Helper_DAL.ExecuteNonQuery(mstrcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Public Shared Sub blanquearPassword(ByVal pid As Integer, ByVal ppassword As String)
            Dim mstrcomm = "UPDATE usuario SET [password] = '" & ppassword & "', [intentos_inc] = 0, [password_reset] = 1 WHERE id_usuario = " & pid
            Try
                Helper_DAL.ExecuteNonQuery(mstrcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Public Shared Sub grabarNuevaPassword(ByVal pid As Integer, ByVal ppassword As String)
            Dim mstrcomm = "UPDATE usuario SET [password] = '" & ppassword & "', [intentos_inc] = 0, [password_reset] = 0 WHERE id_usuario = " & pid
            Try
                Helper_DAL.ExecuteNonQuery(mstrcomm)
            Catch ex As Exception
                Throw ex
            End Try

        End Sub


        Shared Function ObtenerProximoId() As Integer
            Dim maxid As Integer = 0
            Try
                maxid = Helper_DAL.ObtenerProximoId("usuario")
            Catch ex As Exception
                Throw ex
            End Try
            Return maxid
        End Function

    End Class

End Namespace

